﻿using System;
using System.Collections.Generic;
using System.Text;
using KPIS.DBM;
using System.Configuration;
using KPIS.GERP.GIMS.MODEL;
using System.Data;


namespace KPIS.GERP.GIMS.DAL
{
    public class D_AssetLocation
    {
        DBManager dbManager = new DBManager(DataProvider.MySQL, ConfigurationSettings.AppSettings["ConnectionString"].ToString());
        ConvertNullable cn = new ConvertNullable();

        public IList<M_AssetLocation> GetListAssetCode(string asset_code)
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_AssetLocation> infoList = new List<M_AssetLocation>();
            try
            {
                dbManager.Open();
                strSQL = " SELECT location_seq,asset_code,from_job_site_seq,from_wh_seq "
                        +" ,from_other,to_job_site_seq,to_wh_seq,to_other,location_start_date "
                        +" ,location_end_date,remark,al.record_status "
                        +" ,CASE WHEN js.job_site_name is not null THEN js.job_site_name WHEN wh.icm_wh_name THEN  wh.icm_wh_name else from_other end from_location "
                        +" ,CASE WHEN js1.job_site_name is not null THEN js1.job_site_name WHEN wh1.icm_wh_name THEN wh1.icm_wh_name else to_other end to_location "
                        +" FROM icm_asset_location al "
                        +" LEFT JOIN hrm_mp_job_sites js ON al.from_job_site_seq = js.job_site_seq "
                        +" LEFT JOIN icm_mas_warehouse wh ON al.from_wh_seq = wh.icm_wh_seq "
                        +" LEFT JOIN hrm_mp_job_sites js1 ON al.to_job_site_seq = js1.job_site_seq "
                        +" LEFT JOIN icm_mas_warehouse wh1 ON al.to_wh_seq = wh1.icm_wh_seq "
                        +" WHERE al.record_status<>'D' and al.asset_code LIKE '%' '" + asset_code + "'  '%' "
                        +" ORDER BY location_start_date DESC ";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_AssetLocation info = new M_AssetLocation();
                        info.location_seq = Convert.ToInt32(drc[i]["location_seq"]);
                        info.asset_code = drc[i]["asset_code"].ToString();
                        info.from_job_site_seq = cn.ConvertNullInt(drc[i]["from_job_site_seq"].ToString());
                        info.from_wh_seq = cn.ConvertNullInt(drc[i]["from_wh_seq"]);
                        info.from_other = drc[i]["from_other"].ToString();
                        info.to_job_site_seq = cn.ConvertNullInt(drc[i]["to_job_site_seq"].ToString());
                        info.to_wh_seq = cn.ConvertNullInt(drc[i]["to_wh_seq"]);
                        info.to_other = drc[i]["to_other"].ToString();
                        info.location_start_date = Convert.ToDateTime(drc[i]["location_start_date"]);
                        info.location_end_date = cn.ConvertNullDateTime(drc[i]["location_end_date"]);
                        info.remark = drc[i]["remark"].ToString();
                        info.from_location_name = drc[i]["from_location"].ToString();
                        info.to_location_name = drc[i]["to_location"].ToString();
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public M_AssetLocation GetListFillBySeq(string location_seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            M_AssetLocation info = new M_AssetLocation();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@location_seq", location_seq);

                strSQL = " SELECT location_seq,asset_code,from_job_site_seq,from_wh_seq "
                        + " ,from_other,to_job_site_seq,to_wh_seq,to_other,location_start_date "
                        + " ,location_end_date,remark,al.record_status "
                        + " ,CASE WHEN js.job_site_name is not null THEN js.job_site_name WHEN wh.icm_wh_name THEN  wh.icm_wh_name else from_other end from_location "
                        + " ,CASE WHEN js1.job_site_name is not null THEN js1.job_site_name WHEN wh1.icm_wh_name THEN wh1.icm_wh_name else to_other end to_location "
                        + " ,js.job_site_name as job_site_from_name,js1.job_site_name as job_site_to_name,wh.icm_wh_name as wh_from_name,wh1.icm_wh_name as wh_to_name "
                        + " FROM icm_asset_location al "
                        + " LEFT JOIN hrm_mp_job_sites js ON al.from_job_site_seq = js.job_site_seq "
                        + " LEFT JOIN icm_mas_warehouse wh ON al.from_wh_seq = wh.icm_wh_seq "
                        + " LEFT JOIN hrm_mp_job_sites js1 ON al.to_job_site_seq = js1.job_site_seq "
                        + " LEFT JOIN icm_mas_warehouse wh1 ON al.to_wh_seq = wh1.icm_wh_seq "
                        + " WHERE al.record_status<>'D' and location_seq = @location_seq ";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    info.location_seq = Convert.ToInt32(drc[0]["location_seq"]);
                    info.asset_code = drc[0]["asset_code"].ToString();
                    info.from_job_site_seq = cn.ConvertNullInt(drc[0]["from_job_site_seq"].ToString());
                    info.from_wh_seq = cn.ConvertNullInt(drc[0]["from_wh_seq"]);
                    info.from_other = drc[0]["from_other"].ToString();
                    info.to_job_site_seq = cn.ConvertNullInt(drc[0]["to_job_site_seq"].ToString());
                    info.to_wh_seq = cn.ConvertNullInt(drc[0]["to_wh_seq"]);
                    info.to_other = drc[0]["to_other"].ToString();
                    info.location_start_date = Convert.ToDateTime(drc[0]["location_start_date"]);
                    info.location_end_date = cn.ConvertNullDateTime(drc[0]["location_end_date"]);
                    info.remark = drc[0]["remark"].ToString();
                    info.from_location_name = drc[0]["from_location"].ToString();
                    info.to_location_name = drc[0]["to_location"].ToString();
                    info.job_site_from_name = drc[0]["job_site_from_name"].ToString();
                    info.job_site_to_name = drc[0]["job_site_to_name"].ToString();
                    info.wh_from_name = drc[0]["wh_from_name"].ToString();
                    info.wh_to_name = drc[0]["wh_to_name"].ToString();
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return info;
        }

        public void Insert_Asset_Location(M_AssetLocation info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(13);
                dbManager.AddParameters(0, "@asset_code", info.asset_code);
                dbManager.AddParameters(1, "@from_job_site_seq", info.from_job_site_seq);
                dbManager.AddParameters(2, "@from_wh_seq", info.from_wh_seq);
                dbManager.AddParameters(3, "@from_other", info.from_other);
                dbManager.AddParameters(4, "@to_job_site_seq", info.to_job_site_seq);
                dbManager.AddParameters(5, "@to_wh_seq", info.to_wh_seq);
                dbManager.AddParameters(6, "@to_other", info.to_other);
                dbManager.AddParameters(7, "@location_start_date", info.location_start_date);
                dbManager.AddParameters(8, "@location_end_date", info.location_end_date);
                dbManager.AddParameters(9, "@remark", info.remark);
                dbManager.AddParameters(10, "@record_status", info.record_status);
                dbManager.AddParameters(11, "@created_by", info.created_by);
                dbManager.AddParameters(12, "@created_when", info.created_when);

                strSQL = " INSERT INTO icm_asset_location "
                        + " (asset_code,from_job_site_seq,from_wh_seq,from_other,to_job_site_seq "
                        + " ,to_wh_seq,to_other,location_start_date,location_end_date "
                        + " ,remark,record_status,created_by,created_when) "
                        + " VALUES "
                        + " (@asset_code,@from_job_site_seq,@from_wh_seq,@from_other,@to_job_site_seq "
                        + " ,@to_wh_seq,@to_other,@location_start_date,@location_end_date "
                        + " ,@remark,@record_status,@created_by,@created_when) ";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Update_Asset_Locaion(M_AssetLocation info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(14);
                dbManager.AddParameters(0, "@location_seq", info.location_seq);
                dbManager.AddParameters(1, "@asset_code", info.asset_code);
                dbManager.AddParameters(2, "@from_job_site_seq", info.from_job_site_seq);
                dbManager.AddParameters(3, "@from_wh_seq", info.from_wh_seq);
                dbManager.AddParameters(4, "@from_other", info.from_other);
                dbManager.AddParameters(5, "@to_job_site_seq", info.to_job_site_seq);
                dbManager.AddParameters(6, "@to_wh_seq", info.to_wh_seq);
                dbManager.AddParameters(7, "@to_other", info.to_other);
                dbManager.AddParameters(8, "@location_start_date", info.location_start_date);
                dbManager.AddParameters(9, "@location_end_date", info.location_end_date);
                dbManager.AddParameters(10, "@remark", info.remark);
                dbManager.AddParameters(11, "@record_status", info.record_status);
                dbManager.AddParameters(12, "@updated_by", info.updated_by);
                dbManager.AddParameters(13, "@updated_when", info.updated_when);

                strSQL = " UPDATE icm_asset_location "
                        + " SET asset_code = @asset_code "
                        + " ,from_job_site_seq = @from_job_site_seq "
                        + " ,from_wh_seq = @from_wh_seq "
                        + " ,from_other =@from_other "
                        + " ,to_job_site_seq = @to_job_site_seq "
                        + " ,to_wh_seq = @to_wh_seq "
                        + " ,to_other = @to_other "
                        + " ,location_start_date = @location_start_date "
                        + " ,location_end_date = @location_end_date "
                        + " ,remark = @remark "
                        + " ,record_status = @record_status "
                        + " ,updated_by = @updated_by "
                        + " ,updated_when = @updated_when "
                        + " WHERE location_seq = @location_seq ";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Delete_Locaion(M_AssetLocation info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(5);
                dbManager.AddParameters(0, "@location_seq", info.location_seq);
                dbManager.AddParameters(1, "@record_status", info.record_status);
                dbManager.AddParameters(2, "@location_end_date", info.location_end_date);
                dbManager.AddParameters(3, "@updated_by", info.updated_by);
                dbManager.AddParameters(4, "@updated_when", info.updated_when);

                strSQL = " UPDATE icm_asset_location "
                        + " SET record_status = @record_status "
                        + " ,location_end_date = @location_end_date "
                        + " ,updated_by = @updated_by "
                        + " ,updated_when = @updated_when "
                        + " WHERE location_seq = @location_seq ";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Delete_Asset_Locaion(string asset_code_start, string asset_code_finish,string remark)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);
                dbManager.AddParameters(0, "@asset_code_start", asset_code_start);
                dbManager.AddParameters(1, "@asset_code_finish", asset_code_finish);
                dbManager.AddParameters(2, "@remark", remark);

                strSQL =  " UPDATE FROM icm_asset_location "
                        + " SET record_status = 'D' "
                        + " ,remark = @remark "
                        + " WHERE asset_code >=  '@asset_code_start' AND "
                        + " asset_code <= '@asset_code_finish' ";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
        }


    }
}
